﻿Imports ccapps.Web.page
Imports System.Data.OracleClient

Namespace persistent
    Public Class ServiceHistory
        Private Shared ConnStr As String = GlobalVar.OraConnStr

        Private Shared dbPackageName As String = "eu_bill.ServiceHistory."

        Public Shared Function getcustomerInfoFromCustcode(ByVal pin_custcode As String) As DataTable

            Dim command As New OracleCommand(dbPackageName & "GetCustInfoFromCustcode")
            command.Parameters.Add("pin_custcode", OracleType.VarChar).Value = pin_custcode
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output
            'LOGLAMA
            Dim Query As String = "Select * from dual "
            Dim gen As Object = New GPOPrepaidToAveaPostpaidCustomerCreate()
            persistent.myDBConn.ORAScalar_LOG(Query, pin_custcode, "null", "null", "null", "Select(btnCheckCustCode_Clic)", "ServiceHistory", gen.Session("user"))
            Return ExecuteDataTable(command)


        End Function
        Public Shared Function getcustomerInfoFromMSISDN(ByVal pin_msisdn As String) As DataTable

            Dim command As New OracleCommand(dbPackageName & "getcustomerInfoFromMSISDN")
            command.Parameters.Add("pin_msisdn", OracleType.VarChar).Value = pin_msisdn
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output
            'LOGLAMA
            Dim Query As String = "Select * from dual "
            Dim gen As Object = New GPOPrepaidToAveaPostpaidCustomerCreate()
            persistent.myDBConn.ORAScalar_LOG(Query, "null", "null", "null", pin_msisdn, "Select(btnCheckMsisdn_Clic)", "ServiceHistory", gen.Session("user"))
            Return ExecuteDataTable(command)


        End Function
        'procedure ServicePackList (pin_tmcode IN integer, p_cursor OUT r_cursor_type)
        Public Shared Function ServicePackList(ByVal pin_tmcode As Integer) As DataTable

            Dim command As New OracleCommand(dbPackageName & "ServicePackList")
            command.Parameters.Add("pin_tmcode", OracleType.Int32).Value = pin_tmcode
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output
            Return ExecuteDataTable(command)


        End Function
        'procedure ContractReason (pin_co_id IN integer, p_cursor OUT r_cursor_type)
        Public Shared Function ContractReason(ByVal pin_co_id As Integer) As DataTable

            Dim command As New OracleCommand(dbPackageName & "ContractReason")
            command.Parameters.Add("pin_co_id", OracleType.Int32).Value = pin_co_id
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output
            Return ExecuteDataTable(command)


        End Function
        'procedure ServicePackListFromSpcode (pin_tmcode IN integer,pin_spcode IN integer,pin_coid IN integer, p_cursor OUT r_cursor_type);
        Public Shared Function ServicePackListFromSpcode(ByVal pin_tmcode As Integer, ByVal pin_spcode As Integer, ByVal pin_coid As Integer) As DataTable

            Dim command As New OracleCommand(dbPackageName & "ServicePackListFromSpcode")
            command.Parameters.Add("pin_tmcode", OracleType.Int32).Value = pin_tmcode
            command.Parameters.Add("pin_spcode", OracleType.Int32).Value = pin_spcode
            command.Parameters.Add("pin_coid", OracleType.Int32).Value = pin_coid
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output
            Return ExecuteDataTable(command)


        End Function
      
        'procedure ServiceHistControl ( pin_coid IN integer,pin_sncode IN integer,pin_profileid in integer, p_cursor OUT r_cursor_type)
        Public Shared Function ServiceHistControl(ByVal pin_coid As Integer, ByVal pin_sncode As Integer, ByVal pin_profileid As Integer) As DataTable

            Dim command As New OracleCommand(dbPackageName & "ServiceHistControl")
            command.Parameters.Add("pin_profileid", OracleType.Int32).Value = pin_profileid
            command.Parameters.Add("pin_coid", OracleType.Int32).Value = pin_coid
            command.Parameters.Add("pin_sncode", OracleType.Int32).Value = pin_sncode
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output
            Return ExecuteDataTable(command)


        End Function
        'procedure ServiceHist2 ( pin_coid IN integer,pin_sncode IN integer,pin_spcode IN integer,pin_tmcode IN integer, p_cursor OUT r_cursor_type)
        Public Shared Function ServiceHist2(ByVal pin_coid As Integer, ByVal pin_sncode As Integer, pin_spcode As Integer, pin_tmcode As Integer) As DataTable

            Dim command As New OracleCommand(dbPackageName & "ServiceHist2")
            command.Parameters.Add("pin_coid", OracleType.Int32).Value = pin_coid
            command.Parameters.Add("pin_sncode", OracleType.Int32).Value = pin_sncode
            command.Parameters.Add("pin_spcode", OracleType.Int32).Value = pin_spcode
            command.Parameters.Add("pin_tmcode", OracleType.Int32).Value = pin_tmcode
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output
            Return ExecuteDataTable(command)


        End Function
        ' procedure ServiceHistExtraInfo ( pin_coid IN integer,pin_sndes IN varchar2,pin_tmcode IN integer,pin_profiledes in varchar2, p_cursor OUT r_cursor_type)
        Public Shared Function ServiceHistExtraInfo(ByVal pin_coid As Integer, ByVal pin_sndes As Integer, pin_tmcode As Integer, pin_profiledes As String) As DataTable

            Dim command As New OracleCommand(dbPackageName & "ServiceHistExtraInfo")
            command.Parameters.Add("pin_coid", OracleType.Int32).Value = pin_coid
            command.Parameters.Add("pin_sndes", OracleType.Int32).Value = pin_sndes
            command.Parameters.Add("pin_tmcode", OracleType.Int32).Value = pin_tmcode
            command.Parameters.Add("pin_profiledes", OracleType.VarChar).Value = pin_profiledes
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output
            Return ExecuteDataTable(command)


        End Function
        'procedure RpeFuNameList (pin_co_id IN integer,pin_profiledes in varchar2, p_cursor OUT r_cursor_type)
        Public Shared Function RpeFuNameList(ByVal pin_coid As Integer, ByVal pin_profiledes As String) As DataTable

            Dim command As New OracleCommand(dbPackageName & "RpeFuNameList")
            command.Parameters.Add("pin_co_id", OracleType.Int32).Value = pin_coid
            command.Parameters.Add("pin_profiledes", OracleType.VarChar).Value = pin_profiledes
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output
            Return ExecuteDataTable(command)


        End Function

        'procedure ServiceHistDetay ( pin_spcode IN integer,pin_sncode IN integer, p_cursor OUT r_cursor_type);
        'procedure ServiceHistDetay ( pin_spcode IN integer,pin_sndes IN varchar2, p_cursor OUT r_cursor_type)
        Public Shared Function ServiceHistDetay(ByVal pin_spcode As Integer, ByVal pin_sndes As String) As DataTable

            Dim command As New OracleCommand(dbPackageName & "ServiceHistDetay")
            command.Parameters.Add("pin_spcode", OracleType.Int32).Value = pin_spcode
            command.Parameters.Add("pin_sndes", OracleType.VarChar).Value = pin_sndes
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output
            Return ExecuteDataTable(command)


        End Function
        Public Shared Function ServiceHist(ByVal pin_coid As Integer, ByVal pin_tmcode As Integer, ByVal pin_spcode As Integer) As DataTable

            Dim command As New OracleCommand(dbPackageName & "ServiceHist")
            command.Parameters.Add("pin_coid", OracleType.Int32).Value = pin_coid
            command.Parameters.Add("pin_tmcode", OracleType.Int32).Value = pin_tmcode
            command.Parameters.Add("pin_spcode", OracleType.Int32).Value = pin_spcode
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output
            Return ExecuteDataTable(command)


        End Function

        'procedure ServiceHistExtraInfoStatus ( pin_coid IN integer,pin_sncode IN integer,pin_profileid in integer, p_cursor OUT r_cursor_type);
        Public Shared Function ServiceHistExtraInfoStatus(ByVal pin_coid As Integer, ByVal pin_sncode As Integer, ByVal pin_profileid As Integer) As DataTable

            Dim command As New OracleCommand(dbPackageName & "ServiceHistExtraInfoStatus")
            command.Parameters.Add("pin_coid", OracleType.Int32).Value = pin_coid
            command.Parameters.Add("pin_sncode", OracleType.Int32).Value = pin_sncode
            command.Parameters.Add("pin_profileid", OracleType.Int32).Value = pin_profileid
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output
            Return ExecuteDataTable(command)


        End Function

        'procedure GetCustInfo2FromCustcode (pin_custcode IN Varchar2, p_cursor OUT r_cursor_type)
        Public Shared Function GetCustInfo2FromCustcode(ByVal pin_custcode As String) As DataTable

            Dim command As New OracleCommand(dbPackageName & "GetCustInfo2FromCustcode")
            command.Parameters.Add("pin_custcode", OracleType.VarChar).Value = pin_custcode
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output
            'LOGLAMA
            Dim Query As String = "Select * from dual "
            Dim gen As Object = New GPOPrepaidToAveaPostpaidCustomerCreate()
            persistent.myDBConn.ORAScalar_LOG(Query, pin_custcode, "null", "null", "null", "Select(btnCheckCustCode_Clic)", "ServiceHistory", gen.Session("user"))
            Return ExecuteDataTable(command)


        End Function
        Public Shared Function GetCustInfo2FromMSISDN(ByVal pin_msisdn As String) As DataTable

            Dim command As New OracleCommand(dbPackageName & "GetCustInfo2FromMSISDN")
            command.Parameters.Add("pin_msisdn", OracleType.VarChar).Value = pin_msisdn
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output
            'LOGLAMA
            Dim Query As String = "Select * from dual "
            Dim gen As Object = New GPOPrepaidToAveaPostpaidCustomerCreate()
            persistent.myDBConn.ORAScalar_LOG(Query, "null", "null", "null", pin_msisdn, "Select(btnCheckMdisdn_Clic)", "ServiceHistory", gen.Session("user"))
            Return ExecuteDataTable(command)


        End Function
        'procedure ServiceHistExtraInfo2 (pin_profile_id IN integer,pin_sncode IN integer,pin_coid IN integer, p_cursor OUT r_cursor_type)
        Public Shared Function ServiceHistExtraInfo2(ByVal pin_profile_id As Integer, ByVal pin_sncode As Integer, ByVal pin_coid As Integer) As DataTable

            Dim command As New OracleCommand(dbPackageName & "ServiceHistExtraInfo2")
            command.Parameters.Add("pin_profile_id", OracleType.Int32).Value = pin_profile_id
            command.Parameters.Add("pin_sncode", OracleType.Int32).Value = pin_sncode
            command.Parameters.Add("pin_coid", OracleType.Int32).Value = pin_coid
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output
            'LOGLAMA
            'Dim Query As String = "Select * from dual "
            'Dim gen As Object = New GPOPrepaidToAveaPostpaidCustomerCreate()
            ''persistent.myDBConn.ORAScalar_LOG(Query, pin_custcode, "null", "null", "null", "Select(btnCheckCustCode_Clic)", "PriceGroupChange", gen.Session("user"))
            Return ExecuteDataTable(command)


        End Function
        'procedure ServiceNameList (pin_co_id IN integer,pin_sncode in integer, p_cursor OUT r_cursor_type); 
        Public Shared Function ServiceNameList(ByVal pin_co_id As Integer, ByVal pin_sncode As Integer) As DataTable

            Dim command As New OracleCommand(dbPackageName & "ServiceNameList")
            command.Parameters.Add("pin_co_id", OracleType.Int32).Value = pin_co_id
            command.Parameters.Add("pin_sncode", OracleType.Int32).Value = pin_sncode
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output
            'LOGLAMA
            'Dim Query As String = "Select * from dual "
            'Dim gen As Object = New GPOPrepaidToAveaPostpaidCustomerCreate()
            ''persistent.myDBConn.ORAScalar_LOG(Query, pin_custcode, "null", "null", "null", "Select(btnCheckCustCode_Clic)", "PriceGroupChange", gen.Session("user"))
            Return ExecuteDataTable(command)


        End Function
        'procedure ServiceNameList2 (pin_co_id IN integer,pin_sncode in integer,pin_prm_id in integer, p_cursor OUT r_cursor_type)
        Public Shared Function ServiceNameList2(ByVal pin_co_id As Integer, ByVal pin_sncode As Integer, ByVal pin_prm_id As Integer) As DataTable

            Dim command As New OracleCommand(dbPackageName & "ServiceNameList2")
            command.Parameters.Add("pin_co_id", OracleType.Int32).Value = pin_co_id
            command.Parameters.Add("pin_sncode", OracleType.Int32).Value = pin_sncode
            command.Parameters.Add("pin_prm_id", OracleType.Int32).Value = pin_prm_id
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output
            'LOGLAMA
            'Dim Query As String = "Select * from dual "
            'Dim gen As Object = New GPOPrepaidToAveaPostpaidCustomerCreate()
            ''persistent.myDBConn.ORAScalar_LOG(Query, pin_custcode, "null", "null", "null", "Select(btnCheckCustCode_Clic)", "PriceGroupChange", gen.Session("user"))
            Return ExecuteDataTable(command)


        End Function
        'procedure ServiceNameList3(pin_co_id IN integer,pin_sncode in integer,pin_prm_id in integer,pin_profile_id in integer, p_cursor OUT r_cursor_type)
        Public Shared Function ServiceNameList3(ByVal pin_co_id As Integer, ByVal pin_sncode As Integer, ByVal pin_prm_id As Integer, ByVal pin_profile_id As String) As DataTable

            Dim command As New OracleCommand(dbPackageName & "ServiceNameList3")
            command.Parameters.Add("pin_co_id", OracleType.Int32).Value = pin_co_id
            command.Parameters.Add("pin_sncode", OracleType.Int32).Value = pin_sncode
            command.Parameters.Add("pin_prm_id", OracleType.Int32).Value = pin_prm_id
            command.Parameters.Add("pin_profile_id", OracleType.VarChar).Value = pin_profile_id
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output
            'LOGLAMA
            'Dim Query As String = "Select * from dual "
            'Dim gen As Object = New GPOPrepaidToAveaPostpaidCustomerCreate()
            ''persistent.myDBConn.ORAScalar_LOG(Query, pin_custcode, "null", "null", "null", "Select(btnCheckCustCode_Clic)", "PriceGroupChange", gen.Session("user"))
            Return ExecuteDataTable(command)


        End Function
        'procedure GetCustInfoFromCoid (pin_coid IN integer, p_cursor OUT r_cursor_type)
        Public Shared Function GetCustInfoFromCoid(ByVal pin_coid As Integer) As DataTable

            Dim command As New OracleCommand(dbPackageName & "GetCustInfoFromCoid")
            command.Parameters.Add("pin_coid", OracleType.Int32).Value = pin_coid
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output
            'LOGLAMA
            'Dim Query As String = "Select * from dual "
            'Dim gen As Object = New GPOPrepaidToAveaPostpaidCustomerCreate()
            ''persistent.myDBConn.ORAScalar_LOG(Query, pin_custcode, "null", "null", "null", "Select(btnCheckCustCode_Clic)", "PriceGroupChange", gen.Session("user"))
            Return ExecuteDataTable(command)


        End Function


        Public Shared Function ExecuteNonQuery(ByVal command As OracleCommand, ByRef MESSAGE As String)
            Dim connection As New OracleConnection(ConnStr)
            command.Connection = connection
            command.CommandType = CommandType.StoredProcedure

            Try
                connection.Open()
                command.ExecuteNonQuery()
            Catch ex As Exception
                MESSAGE = ex.Message.ToString
            Finally
                connection.Close()
            End Try
        End Function
        Public Shared Function ExecuteDataTable(ByVal command As OracleCommand, Optional ByVal directSql As Boolean = 0) As DataTable
            Dim connection As New OracleConnection(ConnStr)
            Dim dataAdapter As New OracleDataAdapter(command)
            Dim dataTable As New DataTable
            Dim dataSet As New DataSet

            command.Connection = connection

            If Not (directSql) Then
                command.CommandType = CommandType.StoredProcedure
            End If

            Try
                connection.Open()
                dataAdapter.Fill(dataSet)
                dataTable = dataSet.Tables(0)
            Catch ex As Exception
            Finally
                connection.Close()
                dataSet.Dispose()
            End Try

            Return dataTable
        End Function
    End Class
End Namespace
